﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BolsaTrabajo.BusinessEntities;

namespace BolsaTrabajo.DataAccess
{
    public class PostulanteData
    {
        private String stConnection = String.Empty;

        public PostulanteData()
        {
            stConnection = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
        }

        public Postulante CargarEntidad(Int32 id)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Postulante_ListarPorId";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@id", ParameterDirection.Input, SqlDbType.Int, id);

            Postulante item = new Postulante();
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    item.IdPostulante = dr.GetInt32(dr.GetOrdinal("IdPostulante"));
                    item.Nombre = dr.GetString(dr.GetOrdinal("Nombre"));
                    item.ApellidoPaterno = dr.GetString(dr.GetOrdinal("ApellidoPaterno"));
                    item.ApellidoMaterno = dr.GetString(dr.GetOrdinal("ApellidoMaterno"));
                    item.Sexo = dr.GetString(dr.GetOrdinal("Sexo"));
                    item.FechaNacimiento = dr.GetDateTime(dr.GetOrdinal("FechaNacimiento"));
                    item.Correo = dr.GetString(dr.GetOrdinal("Correo"));
                    item.Password = dr.GetString(dr.GetOrdinal("Password"));
                    item.Distrito = dr.GetString(dr.GetOrdinal("Distrito"));
                    item.Direccion = dr.GetString(dr.GetOrdinal("Direccion"));
                    item.TelefonoCelular = dr.GetString(dr.GetOrdinal("TelefonoCelular"));
                    item.TipoDocumento = dr.GetString(dr.GetOrdinal("TipoDocumento"));
                    item.NumeroDocumento = dr.GetString(dr.GetOrdinal("NumeroDocumento"));
                    item.EstadoCivil = dr.GetString(dr.GetOrdinal("EstadoCivil"));
                    item.NivelEstudios = dr.GetString(dr.GetOrdinal("NivelEstudios"));
                    item.Profesion = dr.GetString(dr.GetOrdinal("Profesion"));
                    item.Disponibilidad = dr.GetBoolean(dr.GetOrdinal("Disponibilidad"));
                    item.ExperienciaProfesional = dr.GetString(dr.GetOrdinal("ExperienciaProfesional"));
                    item.NivelIngles = dr.GetString(dr.GetOrdinal("NivelIngles"));
                    item.ExpectativasSalariales = dr.GetString(dr.GetOrdinal("ExpectativasSalariales"));
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return item;
        }

        public bool Registrar(Postulante item)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Postulante_Registrar";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@applicationID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.ApplicationId);
            SQLHelper.AddParam(ref cmd, "@userID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.UserId);
            SQLHelper.AddParam(ref cmd, "@nombre", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Nombre);
            SQLHelper.AddParam(ref cmd, "@apellidoPaterno", ParameterDirection.Input, SqlDbType.VarChar, 50, item.ApellidoPaterno);
            SQLHelper.AddParam(ref cmd, "@apellidoMaterno", ParameterDirection.Input, SqlDbType.VarChar, 50, item.ApellidoMaterno);
            SQLHelper.AddParam(ref cmd, "@sexo", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Sexo);
            SQLHelper.AddParam(ref cmd, "@fechaNacimiento", ParameterDirection.Input, SqlDbType.DateTime, 50, item.FechaNacimiento);
            SQLHelper.AddParam(ref cmd, "@correo", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Correo);
            //SQLHelper.AddParam(ref cmd, "@Password", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Password);
            SQLHelper.AddParam(ref cmd, "@distrito", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Distrito);
            SQLHelper.AddParam(ref cmd, "@direccion", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Direccion);
            SQLHelper.AddParam(ref cmd, "@telefono", ParameterDirection.Input, SqlDbType.VarChar, 50, item.TelefonoCelular);
            SQLHelper.AddParam(ref cmd, "@tipoDocumento", ParameterDirection.Input, SqlDbType.VarChar, 50, item.TipoDocumento);
            SQLHelper.AddParam(ref cmd, "@numeroDocumento", ParameterDirection.Input, SqlDbType.VarChar, 50, item.NumeroDocumento);
            SQLHelper.AddParam(ref cmd, "@estadoCivil", ParameterDirection.Input, SqlDbType.VarChar, 50, item.EstadoCivil);
            SQLHelper.AddParam(ref cmd, "@nivelEstudios", ParameterDirection.Input, SqlDbType.VarChar, 50, item.NivelEstudios);
            SQLHelper.AddParam(ref cmd, "@profesion", ParameterDirection.Input, SqlDbType.VarChar, item.Profesion);
            SQLHelper.AddParam(ref cmd, "@disponibilidad", ParameterDirection.Input, SqlDbType.Bit, item.Disponibilidad);
            SQLHelper.AddParam(ref cmd, "@experienciaProfesional", ParameterDirection.Input, SqlDbType.VarChar, 50, item.ExperienciaProfesional);
            SQLHelper.AddParam(ref cmd, "@nivelIngles", ParameterDirection.Input, SqlDbType.VarChar, 50, item.NivelIngles);
            SQLHelper.AddParam(ref cmd, "@expectativasSalariales", ParameterDirection.Input, SqlDbType.VarChar, 50, item.ExpectativasSalariales);

            bool resultado = false;

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                resultado = true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Dispose();
            }
            return resultado;
        }

        public bool Actualizar(Postulante item)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Postulante_Actualizar";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@idPostulante", ParameterDirection.Input, SqlDbType.Int, item.IdPostulante);
            SQLHelper.AddParam(ref cmd, "@applicationID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.ApplicationId);
            SQLHelper.AddParam(ref cmd, "@userID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.UserId);
            SQLHelper.AddParam(ref cmd, "@nombre", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Nombre);
            SQLHelper.AddParam(ref cmd, "@apellidoPaterno", ParameterDirection.Input, SqlDbType.VarChar, 50, item.ApellidoPaterno);
            SQLHelper.AddParam(ref cmd, "@apellidoMaterno", ParameterDirection.Input, SqlDbType.VarChar, 50, item.ApellidoMaterno);
            SQLHelper.AddParam(ref cmd, "@sexo", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Sexo);
            SQLHelper.AddParam(ref cmd, "@fechaNacimiento", ParameterDirection.Input, SqlDbType.DateTime, 50, item.FechaNacimiento);
            SQLHelper.AddParam(ref cmd, "@correo", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Correo);
            //SQLHelper.AddParam(ref cmd, "@Password", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Password);
            SQLHelper.AddParam(ref cmd, "@distrito", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Distrito);
            SQLHelper.AddParam(ref cmd, "@direccion", ParameterDirection.Input, SqlDbType.VarChar, 50, item.Direccion);
            SQLHelper.AddParam(ref cmd, "@telefono", ParameterDirection.Input, SqlDbType.VarChar, 50, item.TelefonoCelular);
            SQLHelper.AddParam(ref cmd, "@tipoDocumento", ParameterDirection.Input, SqlDbType.VarChar, 50, item.TipoDocumento);
            SQLHelper.AddParam(ref cmd, "@numeroDocumento", ParameterDirection.Input, SqlDbType.VarChar, 50, item.NumeroDocumento);
            SQLHelper.AddParam(ref cmd, "@estadoCivil", ParameterDirection.Input, SqlDbType.VarChar, 50, item.EstadoCivil);
            SQLHelper.AddParam(ref cmd, "@nivelEstudios", ParameterDirection.Input, SqlDbType.VarChar, 50, item.NivelEstudios);
            SQLHelper.AddParam(ref cmd, "@profesion", ParameterDirection.Input, SqlDbType.VarChar, item.Profesion);
            SQLHelper.AddParam(ref cmd, "@disponibilidad", ParameterDirection.Input, SqlDbType.Bit, item.Disponibilidad);
            SQLHelper.AddParam(ref cmd, "@experienciaProfesional", ParameterDirection.Input, SqlDbType.VarChar, 50, item.ExperienciaProfesional);
            SQLHelper.AddParam(ref cmd, "@nivelIngles", ParameterDirection.Input, SqlDbType.VarChar, 50, item.NivelIngles);
            SQLHelper.AddParam(ref cmd, "@expectativasSalariales", ParameterDirection.Input, SqlDbType.VarChar, 50, item.ExpectativasSalariales);

            bool resultado = false;

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                resultado = true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Dispose();
            }
            return resultado;
        }

        public bool Eliminar(Postulante item)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Postulante_Eliminar";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@IdPostulante", ParameterDirection.Input, SqlDbType.Int, item.IdPostulante);

            bool resultado = false;

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                resultado = true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Dispose();
            }
            return resultado;
        }

        public DataTable ListarPostulantes(String postulante, Int32 estado)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmdSql = new SqlCommand();
            cmdSql.CommandTimeout = 216000000;
            DataTable dtListaPostulante = new DataTable();
            SqlDataAdapter adpSol = new SqlDataAdapter();

            try
            {
                cmdSql.Connection = cn;
                {
                    adpSol.SelectCommand = new System.Data.SqlClient.SqlCommand();
                    adpSol.SelectCommand.Connection = cn;
                    adpSol.SelectCommand.CommandText = "usp_Postulante_ListarPorNombreEstado";
                    adpSol.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adpSol.SelectCommand.Parameters.Add(new SqlParameter("@postulante", SqlDbType.VarChar, 50)).Value = postulante;
                    adpSol.SelectCommand.Parameters.Add(new SqlParameter("@estado", SqlDbType.Int)).Value = estado;
                }
                adpSol.Fill(dtListaPostulante);
                dtListaPostulante.TableName = "ListaPostulante";
                return dtListaPostulante;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
                cmdSql.Dispose();
            }
        }

        public Postulante ObtenerEntidadPorDNI(String dni)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Postulante_ListarPorDni";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@dni", ParameterDirection.Input, SqlDbType.VarChar, 50, dni);

            Postulante item = new Postulante();
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    item.IdPostulante = dr.GetInt32(dr.GetOrdinal("IdPostulante"));
                    item.Nombre = dr.GetString(dr.GetOrdinal("Nombre"));
                    item.ApellidoPaterno = dr.GetString(dr.GetOrdinal("ApellidoPaterno"));
                    item.ApellidoMaterno = dr.GetString(dr.GetOrdinal("ApellidoMaterno"));
                    item.Sexo = dr.GetString(dr.GetOrdinal("Sexo"));
                    item.FechaNacimiento = dr.GetDateTime(dr.GetOrdinal("FechaNacimiento"));
                    item.Correo = dr.GetString(dr.GetOrdinal("Correo"));
                    item.Password = dr.GetString(dr.GetOrdinal("Password"));
                    item.Distrito = dr.GetString(dr.GetOrdinal("Distrito"));
                    item.Direccion = dr.GetString(dr.GetOrdinal("Direccion"));
                    item.TelefonoCelular = dr.GetString(dr.GetOrdinal("TelefonoCelular"));
                    item.TipoDocumento = dr.GetString(dr.GetOrdinal("TipoDocumento"));
                    item.NumeroDocumento = dr.GetString(dr.GetOrdinal("NumeroDocumento"));
                    item.EstadoCivil = dr.GetString(dr.GetOrdinal("EstadoCivil"));
                    item.NivelEstudios = dr.GetString(dr.GetOrdinal("NivelEstudios"));
                    item.Profesion = dr.GetString(dr.GetOrdinal("Profesion"));
                    item.Disponibilidad = dr.GetBoolean(dr.GetOrdinal("Disponibilidad"));
                    item.ExperienciaProfesional = dr.GetString(dr.GetOrdinal("ExperienciaProfesional"));
                    item.NivelIngles = dr.GetString(dr.GetOrdinal("NivelIngles"));
                    item.ExpectativasSalariales = dr.GetString(dr.GetOrdinal("ExpectativasSalariales"));
                }
                dr.Close();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return item;
        }

        public Postulante ObtenerEntidadPorCorreo(String correo)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Postulante_ListarPorCorreo";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@correo", ParameterDirection.Input, SqlDbType.NVarChar, 50, correo);

            Postulante item = new Postulante();
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    item.IdPostulante = dr.GetInt32(dr.GetOrdinal("IdPostulante"));
                    item.Nombre = dr.GetString(dr.GetOrdinal("Nombre"));
                    item.ApellidoPaterno = dr.GetString(dr.GetOrdinal("ApellidoPaterno"));
                    item.ApellidoMaterno = dr.GetString(dr.GetOrdinal("ApellidoMaterno"));
                    item.Sexo = dr.GetString(dr.GetOrdinal("Sexo"));
                    item.FechaNacimiento = dr.GetDateTime(dr.GetOrdinal("FechaNacimiento"));
                    item.Correo = dr.GetString(dr.GetOrdinal("Correo"));
                    item.Password = dr.GetString(dr.GetOrdinal("Password"));
                    item.Distrito = dr.GetString(dr.GetOrdinal("Distrito"));
                    item.Direccion = dr.GetString(dr.GetOrdinal("Direccion"));
                    item.TelefonoCelular = dr.GetString(dr.GetOrdinal("TelefonoCelular"));
                    item.TipoDocumento = dr.GetString(dr.GetOrdinal("TipoDocumento"));
                    item.NumeroDocumento = dr.GetString(dr.GetOrdinal("NumeroDocumento"));
                    item.EstadoCivil = dr.GetString(dr.GetOrdinal("EstadoCivil"));
                    item.NivelEstudios = dr.GetString(dr.GetOrdinal("NivelEstudios"));
                    item.Profesion = dr.GetString(dr.GetOrdinal("Profesion"));
                    item.Disponibilidad = dr.GetBoolean(dr.GetOrdinal("Disponibilidad"));
                    item.ExperienciaProfesional = dr.GetString(dr.GetOrdinal("ExperienciaProfesional"));
                    item.NivelIngles = dr.GetString(dr.GetOrdinal("NivelIngles"));
                    item.ExpectativasSalariales = dr.GetString(dr.GetOrdinal("ExpectativasSalariales"));
                }
                dr.Close();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return item;
        }

        public List<Postulante> ListarCabeceraPorAvisoEmpresa(Int32 idAvisoTrabajo, Int32 idEmpresa)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Postulante_CabeceraListarPorAvisoEmpresa";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@idAvisoTrabajo", ParameterDirection.Input, SqlDbType.Int, idAvisoTrabajo);
            SQLHelper.AddParam(ref cmd, "@idEmpresa", ParameterDirection.Input, SqlDbType.Int, idEmpresa);

            List<Postulante> lista = new List<Postulante>();
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (dr.Read())
                {
                    Postulante item = new Postulante();
                    item.IdPostulante = dr.GetInt32(dr.GetOrdinal("idPostulante"));
                    item.NombreCompleto = dr.GetString(dr.GetOrdinal("nombreCompleto"));
                    lista.Add(item);
                    item = null;
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return lista;
        }

        public List<Postulante> ListarDetallePorPostulante(Int32 idAvisoTrabajo, Int32 idEmpresa, Int32 idPostulante)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_Postulante_DetalleListarPorPostulante";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@idAvisoTrabajo", ParameterDirection.Input, SqlDbType.Int, idAvisoTrabajo);
            SQLHelper.AddParam(ref cmd, "@idEmpresa", ParameterDirection.Input, SqlDbType.Int, idEmpresa);
            SQLHelper.AddParam(ref cmd, "@idPostulante", ParameterDirection.Input, SqlDbType.Int, idPostulante);

            List<Postulante> lista = new List<Postulante>();
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (dr.Read())
                {
                    Postulante item = new Postulante();
                    item.IdPostulante = dr.GetInt32(dr.GetOrdinal("idPostulante"));
                    item.Profesion = dr.GetString(dr.GetOrdinal("profesion"));
                    item.NivelEstudios = dr.GetString(dr.GetOrdinal("nivelEstudios"));
                    item.ExperienciaProfesional = dr.GetString(dr.GetOrdinal("experienciaProfesional"));
                    lista.Add(item);
                    item = null;
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return lista;
        }
    }
}
